﻿
CREATE PROCEDURE [dbo].[s_TrackerRequest_New]
	@VIN varchar(17) = NULL
AS
SET NOCOUNT ON;

DECLARE @TrackerRequestID int

IF @VIN > '' BEGIN -- Serach through Installation Requests

/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> SELECT  @TrackerRequestID = tr.ID	FROM t_TrackerRequest tr'
	SELECT  @TrackerRequestID = tr.ID	FROM t_TrackerRequest tr
	INNER JOIN v_Menu_10 m ON tr.StatusID = m.MenuID
	LEFT OUTER JOIN t_Tracker_Car tc ON tr.ID = tc.TrackerRequestID
	WHERE tr.VIN = @VIN AND tc.TrackerRequestID IS NULL AND m.WorkFlowIndex <=614
	ORDER BY m.WorkFlowIndex

	IF @TrackerRequestID IS NULL BEGIN-- Serach through Removal Requests
/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> SELECT  @TrackerRequestID = tr.ID	FROM t_TrackerRequest tr'
		SELECT  @TrackerRequestID = tr.ID	FROM t_TrackerRequest tr
		INNER JOIN v_Menu_10 m ON tr.StatusID = m.MenuID
		INNER JOIN t_Tracker_Car tc ON tr.VIN = tc.VIN
		WHERE tr.VIN = @VIN AND m.WorkFlowIndex BETWEEN 615 AND 625
		ORDER BY m.WorkFlowIndex
	END

	IF @TrackerRequestID IS NULL BEGIN
/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> s_TrackerRequest_New'
		EXEC @TrackerRequestID = s_TrackerRequest_New
/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> UPDATE t_TrackerRequest SET VIN = @VIN WHERE ID = $0',@TrackerRequestID
		UPDATE t_TrackerRequest SET VIN = @VIN WHERE ID = @TrackerRequestID
	END
	ELSE BEGIN
		DECLARE @LoanID int,@DealerName_Loan sysname
/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> SELECT @LoanID = li.LoanID,@DealerName_Loan = li.DealerName'
		SELECT @LoanID = li.LoanID,@DealerName_Loan = li.DealerName
		FROM t_TrackerRequest tr INNER JOIN v_LoanInfo_10 li ON tr.VIN = li.VIN AND li.DealerID <> tr.DealerID
		WHERE tr.VIN = @VIN
		IF @LoanID > 0 BEGIN
			IF @@TRANCOUNT > 0 ROLLBACK TRAN
			DECLARE @ShowLoan nvarchar(1024) SET @ShowLoan = dbo.ShowLoan(@LoanID)
			RAISERROR('Unable to create new reaquest for VIN/Dealer [%s/%s]
because loan [%d] with the same VIN has a different Dealer then existing Tracker Request [%d].%s',
			16,1,@VIN,@DealerName_Loan,@LoanID,@TrackerRequestID,@ShowLoan)
		END
	END

/**/EXEC sh_Debug_Print 's_TrackerRequest_New <> @TrackerRequestID : $0',@TrackerRequestID
	RETURN @TrackerRequestID
END

DECLARE @StartID int SET @StartID = dbo.TrackerRequestStartID()
/**/EXEC sh_Debug_Print 's_TrackerRequest_New (No VIN) <> SELECT  @TrackerRequestID = ID'
SELECT  @TrackerRequestID = ID
--SELECT  *
FROM         t_TrackerRequest
WHERE ISNULL(DealerID,0)<=0 AND
			ISNULL(VIN,'')='' AND
			TechnicianID = 0 AND
			StatusID = @StartID AND
			LoanID IS NULL AND
			DateCreated < DATEADD(mi,-1,GETDATE())

IF NOT @TrackerRequestID IS NULL BEGIN
/**/EXEC sh_Debug_Print 's_TrackerRequest_New (No VIN) <> UPDATE t_TrackerRequest SET DateCreated = GETDATE() WHERE ID = @TrackerRequestID'
	UPDATE t_TrackerRequest SET DateCreated = GETDATE() WHERE ID = @TrackerRequestID
	RETURN @TrackerRequestID
END

/**/EXEC sh_Debug_Print 's_TrackerRequest_New (No VIN) <> INSERT INTO t_TrackerRequest DEFAULT VALUES'
INSERT INTO t_TrackerRequest DEFAULT VALUES 
RETURN IDENT_CURRENT( 't_TrackerRequest' )




